Method for optimizing index, master database node and subscriber database node

ABSTRACT

A method, a master database node and a subscriber database node for optimizing index. The method for optimizing index includes: checking, by a master database node, if role information of an index matches role information of the master database node, when the index is added into the master database node; updating database information by using the index when the role information of the index matches the role information of the master database node. In this invention, unnecessary indexes in the master database node and the subscriber database node are reduced while keeping the schema the same. Furthermore, unnecessary logs transmitted from the master database node to the subscriber database node are reduced.

CROSS-REFERENCE TO RELATED APPLICATIONS

This application is a continuation of International Patent ApplicationNo. PCT/CN2014/076612, filed on Apr. 30, 2014, which claims priority toIndian Patent Application No. IN5329/CHF/2013, filed on Nov. 19, 2013,both of which are hereby incorporated by reference in their entireties.

FIELD OF THE INVENTION

This application relates to database technology, in particular to amethod for optimizing index, a master database node and a subscriberdatabase node.

BACKGROUND

In a relational database, index is the most common way to speed-up aquery to the database. The indexes are associated with tables. Once anindex is associated with a table, the operation on the index (either aread or a write) is implicit. There is no need for the user to have thecognizance of an index.

For recovery and replication purpose, generally RDBMS generates a logwhich describes each operation. This log is known as a redo log or atransactional log or a simply log. In many cases this log is physical innature. Whenever a write operation happens on the database, the logrecords this write operation. This is accomplished by the “log writerprocess”. These logs include: the updates on the tables (the datasegment) and the updates on the indexes associated with the tables (theindex segments).

FIG. 1 is a schematic diagram showing an example of standard operationinside a RDBMS storage engine. As shown in FIG. 1, a simple table withtwo indexes is considered. When a record is inserted into the table, thedatabase process will write to the data segment and the two indexsegments, while logs for each operation are generated.

On the other hand, for requirements of high availability (HA), loadbalancing databases use replication as a standard methodology. The mostcommon form of replication implementation is “log shipping”.

FIG. 2 is a schematic diagram showing an example of log shipping betweena master database node and a subscriber database node. As shown in FIG.2, in this solution, the designate master database node continuouslyships its logs (such as redo logs) to the subscriber database node. Thesubscriber database node applies these logs on the database tosynchronize with the master database node. The shipped logs includeupdates on the data segments and the index segments.

For purposes of seamless switch-over (or fail-over), it is desirablethat the schemas in the master database node and the subscriber databasenode are kept the same. Most of HA systems in communication domain andenterprise domains use a master database node which is Read-Write andone or more subscriber database nodes which are Read-Only.

Nowadays, log shipping from the master database node to the subscriberdatabase node is a normal feature for all database replications.Specialized replication solutions like GoldenGate (such as in Oracle)can synchronize databases in heterogeneous topologies. A similarsolution is offered by using a translation middleware in [reference 1].These solutions use logical replication based on SQL statements whichare quite slow compared to the synchronization based on log shipping.

[Reference 2] has proposed an invention that: the data, which is shippedfrom the master database node, is re-interpreted in the subscriberdatabase nodes semantically, and then is applied to the databases. Thissolution potentially can reduce the size of log which is being shippedfrom the master database node.

[Reference 3] has proposed an invention that: the master database nodeships a log to a standby database node. The standby database node has amapped table to the master DB's table (known as the federated tablemapping). The change is applied to the federated table.

However, all the above said solutions have the following two weaknesses:(1) the re-interpretation is done in the subscriber database node andthe master database node has no cognizance of this fact, which does nothelp to reduce the master's load; (2) as the two nodes are semanticallynot equal, switch-over is going to be very costly.

[Reference 4] offers an invention which can reduce the load of themaster database node and the subscriber database nodes. This inventioncan also reduce the memory consumption on the master database node. Inthis innovation, the subscriber database node has a set of its ownindexes which are generated by the subscriber database node on receivingthe logs from the master database node. On switch-over these indexes aredropped by new master database node. This solution has the advantage ofeasy switch-over and memory optimization on the master database node.

However this solution has 3 issues: (1) it does not have a mechanism toreduce redundant indexes on the subscriber database nodes; (2) itrequires the subscriber database node to identify which indexes need tobe populated additionally, and which cannot be typically accomplished inthe storage engine layer alone; (3) it puts the onus index creation onvarious nodes based on roles to the administrator.

[reference 1] “Middleware-based database replication: the gaps betweentheory and practice”, Proceedings of the 2008 ACM SIGMOD internationalconference on Management of data, Pages 739-752.

[reference 2] WIPO Patent Application WO/2007/037984 (also U.S. Pat. No.8,290,910), titled “SEMANTIC REPLICATION”.

[reference 3] WIPO Patent Application WO/2010/073110, titled “SELECTIVEDATABASE REPLICATION”.

[reference 4] US Patent Application 20110320404 titled “STANDBY INDEX INPHYSICAL DATA REPLICATION”.

SUMMARY

Embodiments of the present invention pertain to a method for optimizingindex, a master database node and subscriber database node. The objectof the invention is to reduce unnecessary indexes in the master databasenode and the subscriber database node while keeping the schema the same.

According a first aspect of the embodiments of the present invention, amethod for optimizing index is provided, the method includes:

checking, by a master database node, if role information of an indexmatches role information of the master database node, when the index isadded into the master database node;

updating, by the master database node, database information by using theindex when the role information of the index matches the roleinformation of the master database node.

According another aspect of the embodiments of the present invention,wherein before checking if role information of an index matches roleinformation of the master database node, the method further comprises:

adding the role information for the master database node.

According another aspect of the embodiments of the present invention,wherein before checking if role information of an index matches roleinformation of the master database node, the method further comprises:

creating the index by using Data Definition Language;updating the index with the role information.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

checking, by the master database node, if the role information of theindex matches the role information of a subscriber database node;sending, by the master database node, the index to the subscriberdatabase node when the role information of the index matches the roleinformation of the subscriber database node, so as to synchronize theindex with the subscriber database node.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

receiving, by the master database node, the role information of thesubscriber database node.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

checking, by the master database node, if the role information of theindex matches the role information of the master database node, when arecord need to be inserted into a table associated with the index;generating dummy redo information for the record when the roleinformation of the index does not match the role information of themaster database node.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

checking, by the master database node, if the role information of theindex matches the role information of a subscriber database node;sending, by the master database node, the dummy redo information to thesubscriber database node when the role information of the index matchesthe role information of the subscriber database node.

According another aspect of the embodiments of the present invention,wherein when the role information of the index matches the roleinformation of the master database node, the method further comprises:

inserting, by the master database node, the record into the tableassociated with the index; generating redo information for the record.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

checking, by the master database node, if the role information of theindex matches the role information of a subscriber database node;sending, by the master database node, the redo information to thesubscriber database node when the role information of the index matchesthe role information of the subscriber database node.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

checking, by the master database node, if the role information of theindex matches updated role information of the master database node, whenthe role information of the master database node is updated;populating, by the master database node, the index when the roleinformation of the index matches the updated role information of themaster database node.

According a second aspect of the embodiments of the present invention, amethod for optimizing index is provided, comprising:

updating, by a subscriber database node, role information of thesubscriber database node when the subscriber database node is added intoa cluster containing a master database node;sending, by the subscriber database node, the role information of thesubscriber database node to the master database node.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

receiving, by the subscriber database node, an index from the masterdatabase node; synchronizing the index with the master database node.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

receiving, by the subscriber database node, redo information from themaster database node; updating relevant segment based on the redoinformation, when the redo information is index redo information.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

updating the index with the redo information, when the redo informationis not index redo information and not dummy redo information.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

generating index data and updating the index according to the redoinformation, when the redo information is not index redo information andis dummy redo information.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

checking, by the subscriber database node, if the role information ofthe index matches updated role information of the subscriber databasenode, when the role information of the subscriber database node isupdated;populating, by the subscriber database node, the index when the roleinformation of the index matches the updated role information of thesubscriber database node.

According another aspect of the embodiments of the present invention,wherein the method further comprises:

truncating, by the subscriber database node, the index when the roleinformation of the index does not match the updated role information ofthe subscriber database node.

According a third aspect of the embodiments of the present invention, amaster database node is provided, comprising:

a first checking unit, configured to check if role information of anindex matches role information of the master database node, when theindex is added into the master database node;a first updating unit, configured to update database information byusing the index when the role information of the index matches the roleinformation of the master database node.

According another aspect of the embodiments of the present invention,wherein the master database node further comprises:

a first adding unit, configured to add the role information for themaster database node.

According another aspect of the embodiments of the present invention,wherein the master database node further comprises:

a first creating unit, configured to create the index by using DataDefinition Language;a second updating unit, configured to update the index with the roleinformation.

According another aspect of the embodiments of the present invention,wherein the master database node further comprises:

a second checking unit, configured to check if the role information ofthe index matches the role information of a subscriber database node;a first sending unit, configured to send the index to the subscriberdatabase node when the role information of the index matches the roleinformation of the subscriber database node, so as to synchronize theindex with the subscriber database node.

According another aspect of the embodiments of the present invention,wherein the master database node further comprises:

-   -   a first receiving unit, configured to receive the role        information of the subscriber database node.

According another aspect of the embodiments of the present invention,wherein the master database node further comprises:

a third checking unit, configured to check if the role information ofthe index matches the role information of the master database node, whena record need to be inserted into a table associated with the index;a first generating unit, configured to generate dummy redo informationfor the record when the role information of the index does not match therole information of the master database node.

According another aspect of the embodiments of the present invention,wherein the master database node further comprises:

a fourth checking unit, configured to check if the role information ofthe index matches the role information of a subscriber database node;a second sending unit, configured to send the dummy redo information tothe subscriber database node when the role information of the indexmatches the role information of the subscriber database node.

According another aspect of the embodiments of the present invention,wherein the master database node further comprises:

a first inserting unit, configured to insert the record into the tableassociated with the index;a second generating unit, configured to generate redo information forthe record.

According another aspect of the embodiments of the present invention,wherein the master database node further comprises:

a fifth checking unit, configured to check if the role information ofthe index matches the role information of a subscriber database node;a third sending unit, configured to send the redo information to thesubscriber database node when the role information of the index matchesthe role information of the subscriber database node.

According another aspect of the embodiments of the present invention,wherein the master database node further comprises:

a sixth checking unit, configured to check if the role information ofthe index matches updated role information of the master database node,when the role information of the master database node is updated;a first populating unit, configured to populate the index when the roleinformation of the index matches the updated role information of themaster database node.

According a fourth aspect of the embodiments of the present invention, asubscriber database node is provided, comprising:

a third updating unit, configured to update role information of thesubscriber database node when the subscriber database node is added intoa cluster containing a master database node;a fourth sending unit, configured to send the role information of thesubscriber database node to the master database node.

According another aspect of the embodiments of the present invention,wherein the subscriber database node further comprises:

a second receiving unit, configured to receive an index from the masterdatabase node;a synchronizing unit, configured to synchronize the index with themaster database node.

According another aspect of the embodiments of the present invention,wherein the subscriber database node further comprises:

a third receiving unit, configured to receive redo information from themaster database node;a fourth updating unit, configured to update relevant segment based onthe redo information, when the redo information is index redoinformation.

According another aspect of the embodiments of the present invention,wherein the subscriber database node further comprises:

a fifth updating unit, configured to update the index with the redoinformation, when the redo information is not index redo information andnot dummy redo information.

According another aspect of the embodiments of the present invention,wherein the subscriber database node further comprises:

a second generating unit, configured to generate index data and updatethe index according to the redo information, when the redo informationis not index redo information and is dummy redo information.

According another aspect of the embodiments of the present invention,wherein the subscriber database node further comprises:

a seventh checking unit, configured to check if the role information ofthe index matches updated role information of the subscriber databasenode, when the role information of the subscriber database node isupdated;a second populating unit, configured to populate the index when the roleinformation of the index matches the updated role information of thesubscriber database node.

According another aspect of the embodiments of the present invention,wherein the subscriber database node further comprises:

a truncating unit, configured to truncate the index when the roleinformation of the index does not match the updated role information ofthe subscriber database node.

The advantages of the present invention exist in that: indexes areassociated with role information, while role information is associatedwith the master database node and the subscriber database node. In thisinvention, unnecessary indexes in the master database node and thesubscriber database node are reduced while keeping the schema the same.Furthermore, unnecessary logs transmitted from the master database nodeto the subscriber database node are reduced.

These and further aspects and features of the present invention will beapparent with reference to the following description and attacheddrawings. In the description and drawings, particular embodiments of theinvention have been disclosed in detail as being indicative of some ofthe ways in which the principles of the invention may be employed, butit is understood that the invention is not limited correspondingly inscope. Rather, the invention includes all changes, modifications andequivalents coming within the spirit and terms of the appended claims.

Features that are described and/or illustrated with respect to oneembodiment may be used in the same way or in a similar way in one ormore other embodiments and/or in combination with or instead of thefeatures of the other embodiments.

It should be emphasized that the term “comprises/comprising” when usedin this specification is taken to specify the presence of statedfeatures, integers, steps or components but does not preclude thepresence or addition of one or more other features, integers, steps,components or groups thereof.

Many aspects of the invention can be better understood with reference tothe following drawings. The components in the drawings are notnecessarily to scale, emphasis instead being placed upon clearlyillustrating the principles of the present invention. To facilitateillustrating and describing some parts of the invention, correspondingportions of the drawings may be exaggerated in size, e.g., made largerin relation to other parts than in an exemplary device actually madeaccording to the invention. Elements and features depicted in onedrawing or embodiment of the invention may be combined with elements andfeatures depicted in one or more additional drawings or embodiments.Moreover, in the drawings, like reference numerals designatecorresponding parts throughout the several views and may be used todesignate like or similar parts in more than one embodiment.

BRIEF DESCRIPTION OF THE DRAWING

The drawings are included to provide further understanding of thepresent invention, which constitute a part of the specification andillustrate the embodiments of the present invention, and are used forsetting forth the principles of the present invention together with thedescription. The same element is represented with the same referencenumber throughout the drawings.

In the drawings:

FIG. 1 is a schematic diagram showing an example of standard operationinside a RDBMS storage engine;

FIG. 2 is a schematic diagram showing an example of log shipping betweena master database node and a subscriber database node;

FIG. 3 is a schematic diagram showing an example of indexes used in amaster database node and a subscriber database node;

FIG. 4 is a flowchart of the method for optimizing index in accordancewith an embodiment of the present invention;

FIG. 5 is a schematic diagram showing an example of indexes used in amaster database node and a subscriber database node;

FIG. 6 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention;

FIG. 7 is a schematic diagram showing an example of adding a role in themaster database node;

FIG. 8 is a schematic diagram showing an example of adding an index inthe master database node;

FIG. 9 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention;

FIG. 10 is a schematic diagram showing an example of adding a subscriberdatabase node to a cluster;

FIG. 11 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention;

FIG. 12 is a schematic diagram showing an example of insert operation inthe master database node;

FIG. 13 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention;

FIG. 14 is a schematic diagram showing an example of applicationscenario 1;

FIG. 15 is a schematic diagram showing an example of tables and indexesinformation of the scenario 1;

FIG. 16 is a schematic diagram showing an example of applicationscenario 2;

FIG. 17 is a schematic diagram showing an example of table and indexesinformation of the scenario 2;

FIG. 18 is a schematic diagram showing an example of applicationscenario 3;

FIG. 19 is a flowchart of the method for optimizing index in accordancewith an embodiment of the present invention;

FIG. 20 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention;

FIG. 21 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention;

FIG. 22 is a schematic diagram showing an example of REDO replay in thesubscriber database node;

FIG. 23 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention;

FIG. 24 is a schematic diagram showing an example of role changehandling in the subscriber database node;

FIG. 25 is a schematic diagram of the master database node in accordancewith an embodiment of the present invention;

FIG. 26 is another schematic diagram of the master database node inaccordance with an embodiment of the present invention;

FIG. 27 is a schematic diagram of the subscriber database node inaccordance with an embodiment of the present invention;

FIG. 28 is another schematic diagram of the subscriber database node inaccordance with an embodiment of the present invention.

DETAILED DESCRIPTION

The many features and advantages of the embodiments are apparent fromthe detailed specification and, thus, it is intended by the appendedclaims to cover all such features and advantages of the embodiments thatfail within the true spirit and scope thereof. Further, since numerousmodifications and changes will readily occur to those skilled in theart, it is not desired to limit the inventive embodiments to the exactconstruction and operation illustrated and described, and accordinglyall suitable modifications and equivalents may be resorted to, fallingwithin the scope thereof.

FIG. 3 is a schematic diagram showing an example of indexes used in amaster database node and a subscriber database node. As shown in FIG. 3,databases with a table and two indexes associated with the table areconsidered. Assume that the master database node is primarily used forwrite and it only uses index-1, and the subscriber database node isread-only and it only uses index-2.

As shown in FIG. 3, the items marked in dark are unnecessary. The priorart suffers from the following few or all issues: unnecessary logs areshipped to the subscriber database nodes and applied; the masterdatabase node is required to generate redo-logs for unnecessary indexes,which causes reduced availability of the master database node; themaster database node and the subscriber database node are required tomaintain unnecessary indexes costing additional memory; the masterdatabase node and the subscriber database node do not share the sameschema whereby switch-over is very costly; the master database node isnot aware of the schema of the subscriber database node, such that thecluster topology is very complex; the replication optimization cannot beperformed trivially by the storage engine alone.

In order to overcome the above mentioned deficiencies, there is aproposed solution of selective maintenance of index information. Theembodiments of the present invention are described as follows inreference to the drawings.

Embodiment 1

This embodiment of the present invention provides a method for methodfor optimizing index, applied in a master database node.

FIG. 4 is a flowchart of the method for optimizing index in accordancewith an embodiment of the present invention; as shown in FIG. 4, themethod includes:

Step 401, a master database node checks if role information of an indexmatches role information of the master database node, when the index isadded into the master database node;

Step 402, the master database node updates database information by usingthe index when the role information of the index matches the roleinformation of the master database node.

In this embodiment, an index, which is created on the master databasenode, is assigned a corresponding role based on its primary usage. Theindex could be termed a read-aid index or a write-aid index. Thischaracteristic determines nature of replication for this index.

In this embodiment, the characteristic of the index is mapped to theassigned characteristic of the master database node. Only if theattribute is mapped, the index will exist on the given node.

FIG. 5 is a schematic diagram showing an example of indexes used in amaster database node and a subscriber database node. As shown in FIG. 5,for example, Index-1 is designated as a “Role-1” index and the Index-2is designated as a “Role-2” index.

As shown in FIG. 5, the master database node, which is designated as a“Role-1” node, ships only the data but not the Index-1 to the subscriberdatabase node. Furthermore, the master database node does not insert anyinformation into Index-2 which is a “Role-2” index.

In practically, Role-1 could be “Write-Only” and the associated indexcould be a “write-aid” index. Similarly Role-2 could be “Read-Only” andthe associated index could be a “read-aid” index.

FIG. 6 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention; as shown in FIG.6, the method includes:

-   -   Step 601, the master database node adds the role information;    -   Step 602, the master database node creates an index by using        Data Definition Language (DDL);    -   Step 603, the master database node updates the index with the        role information;    -   Step 604, the master database node checks if role information of        the index matches role information of the master database node;    -   Step 605, the master database node updates database information        by using the index, when the role information of the index        matches the role information of the master database node.

In this embodiment, telecom systems use multiple copies of the samedatabase to achieve high availability. But many times the same copy ofthe database is used for different service purposes. Hence along withthe usage of a standby database (subscriber database node) forredundancy, the standby database could be used for multiple servicereasons. For example, a standby database may be configured to handlebasic call operations. In this case it requires only information aboutthe phone numbers and mutes. Another standby database may be used forbilling information like bill-plan data, per-minute billing. Theseservices can be considered as different “roles” of the database.

Different services of the database will require different indexes tooptimize the processing. Hence indexes also can be assigned a “role”.Grouping a set of indexes under a “role” and also tagging a subscriberdatabase node with a role can help to synchronize only relevantinformation to the subscriber database node and also to store onlyrelevant information in the master database node. The database softwareis enhanced with storage space for roles. The catalog or system tableshave a field role enabled.

Table 1 has shown system table definitions. As shown in table 1, thesystem tables have the subscriber information and the Index informationadded with the “Role ID” field.

TABLE 1 SysSubscribers SubID IP Port Role ID SysIndex IndexID Idx NameTable — Role ID

In another embodiment, multiple roles could be assigned per index. Alsomultiple roles could be assigned per subscribers. Table 2 has shownother system table definitions. As shown in table 2, multiple rolescould be assigned for index or subscriber database node.

TABLE 2

Furthermore, there could be also a pre-defined set of roles. Forexample, the roles could be typically the role of “read-only” and“primarily-write” for nodes. The roles also could be typically the roleof “read-aid” and “write-aid” for indexes

In this embodiment, a special catalog (system) table may be introducedto handle the roles defined in the master database node. The structureof this system table (SysRoles) is shown in the following table 3.

TABLE 3

To make the search and other optimizations, the indexes corresponding toa given role and subscribers corresponding to a given role are alsotracked in this system table.

In step 601, a role can be added to the database through a special DDLcommand or by configuration in the master database node. Once a role isadded to the master DB, this data is placed in the “SysRoles” table.FIG. 7 is a schematic diagram showing an example of adding a role in themaster database node.

In step 602 and 603, the master database node is assigned a role whichit uses to ensure only the relevant indexes are stored in the masterdatabase node. Index related DDL are enhanced to support “role” as aparameter in a fashion similar to the following two examples:

1. CREATE INDEX idx1 ON table1 (col1) WITH ROLE role1

2. ALTER INDEX idx1 SET ROLE role1

In step 604 and 605, when an index is added to the master database node,the master database node will check if the role is configured in thedatabase. If the role is not configured, or if the role is not mentionedin an index, the default role assigned is “ALL”. After verification ofthe role, the master database node places this index information in theSysRoles table.

Then the master database node checks if its own role corresponds to theindex's role. If the role matches, the master populates this indexfield. FIG. 8 is a schematic diagram showing an example of adding anindex in the master database node.

The above embodiment has described that: indexes are associated withrole information, while role information is associated with the masterdatabase node and the subscriber database node. In this embodiment,unnecessary indexes in the master database node and the subscriberdatabase node are reduced while keeping the schema the same.

In another embodiment, indexes associated with role information areshipped to the subscriber database node from the master database node.In this embodiment, unnecessary logs transmitted from the masterdatabase node to the subscriber database node are reduced.

FIG. 9 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention; as shown in FIG.9, the method includes:

-   -   Step 901, the master database node checks if the role        information of the index matches the role information of a        subscriber database node;    -   Step 902, the master database node sends the index to the        subscriber database node when the role information of the index        matches the role information of the subscriber database node, so        as to synchronize the index with the subscriber database node.

As shown in FIG. 9, the method may further include:

-   -   Step 900, the master database node receives the role information        of the subscriber database node.

FIG. 10 is a schematic diagram showing an example of adding a subscriberdatabase node to a cluster. As shown in FIG. 10, when a subscriberdatabase node is added to the cluster, the subscriber database nodepublishes the “role” to the master database node.

As shown in FIG. 10, the master database node records this informationin the SysSubscribers table. Based on the role, the master database nodereplicates only the relevant information to the subscriber databasenode. This is done by iterating through all the indexes and checking ifthe role of the subscriber database node matches the role of the index.If it matches, the master database node synchronizes the data with thesubscriber database node.

In another embodiment, a record need to be inserted into a tableassociated with the index. In this embodiment, the master database nodetracks the amount of work required to complete before the logs could beshipped to the subscriber database node.

After finishing this work, the logs are shipped. If there is some morework, which is typically to update an index which is relevant to themaster, it continues to do this work. This reduces the latency of thesynchronization of data between the subscriber database node and themaster database node. This makes the master database node and subscriberdatabase node more real-time synchronized.

FIG. 11 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention; as shown in FIG.11, the method includes:

-   -   Step 1101, the master database node checks if the role        information of the index matches the role information of the        master database node;    -   Step 1102, the master database node generates dummy redo        information for the record, when the role information of the        index does not match the role information of the master database        node.

As shown in FIG. 11, the method may further include:

-   -   Step 1103, the master database node checks if the role        information of the index matches the role information of a        subscriber database node;    -   Step 1104, the master database node sends the dummy redo        information to the subscriber database node, when the role        information of the index matches the role information of the        subscriber database node.

As shown in FIG. 11, when the role information of the index matches therole information of the master database node, the method may furtherinclude:

-   -   Step 1105, the master database node inserts the record into the        table associated with the index;    -   Step 1106, the master database node generates redo information        for the record.

As shown in FIG. 11, the method may further include:

-   -   Step 1107, the master database node checks if the role        information of the index matches the role information of a        subscriber database node;    -   Step 1108, the master database node sends the redo information        to the subscriber database node, when the role information of        the index matches the role information of the subscriber        database node.

FIG. 12 is a schematic diagram showing an example of insert operation inthe master database node. As shown in FIG. 12, when a record is insertedin the master database node, the master database node inserts the recordin the relevant table. A redo is generated for this insert operation oftable.

Then the master database node iterates through the list of indexesassociated with the table. The master database node first generates“dummy REDO” for all associated indexes. The dummy REDO is generatedwith the role of the index and the master does not match. Then the“dummy REDO” is shipped to the subscriber database node, if the role ofthe subscriber database node matches that of the role of the index forwhich the “dummy REDO” was generated.

Then the master database node proceeds to insert data into all theindexes whose role matches that of the master database node. REDO logsare generated for these indexes and then shipped to the subscriberdatabase nodes whose role matches the indexes.

In another embodiment, role information of the master database node maybe updated.

FIG. 13 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention; as shown in FIG.13, the method includes:

-   -   Step 1301, the master database node checks if the role        information of the index matches updated role information of the        master database node, when the role information of the master        database node is updated;    -   Step 1302, the master database node populates the index when the        role information of the index matches the updated role        information of the master database node.

In this embodiment, when a fail-over or a switch-over happens, a newmaster database node obtains the master's role from a persistent entitywhich could be a cluster manager or other servers. It can obtain thisdefault role from a configuration file or also from a well-knownlocation. Based on this new role, the master database node will updateits indexes.

In another embodiment, the “role” of the subscriber database node couldbe used as a policy parameter for cluster switch-over and management.The role parameter could be used to determine the priority ofreplication from the master database node.

For example, the reporting role could be inferior to a sales role.Accordingly the nodes having the “sale role” could get the priority inthe replication and also could be prioritized to take over as the masterduring a switch-over.

There are some application scenarios for this invention. However, it isnot limited thereto, and particular implement way may be determined asactually required.

FIG. 14 is a schematic diagram showing an example of applicationscenario 1; FIG. 15 is a schematic diagram showing an example of tablesand indexes information of the scenario 1.

As shown in FIG. 15, the schema is one sample schema for a callprocessing entity. The call processing entity could be a soft switch ora telecom switch.

As shown in FIG. 14, in the scenario for a call processing entity like atelecom switch, there are functionalities to handle basic voice calls,value added service calls and also to handle billing systems.

In this scenario, the base data on which they operate are same. Howeverthe views which are used to operate are quite different. For example, asingle table could contain all the information about the billing,service subscription and number routing details. The billing informationmay not be required by any entity other than the service call processingentity.

In this scenario, the database deployment is optimized by engagingdifferent indexes for different entities. This has an advantage ofreducing the memory usage and at the same time the DBs can be used forHA.

FIG. 16 is a schematic diagram showing an example of applicationscenario 2; FIG. 17 is a schematic diagram showing an example of tableand indexes information of the scenario 2.

As shown in FIG. 17, the schema is one sample schema for a simple itemsmanagement entity. There is one table which contains the itemsinformation. The table contains 3 indexes focused on Item ID, Item Nameand Item Quantity.

As shown in FIG. 16, In the scenario there are 3 entities which use thereplicated database. There is one entity the data-entry operation. Thisentity is tagged with the supply-chain and also for price control. Theitems information is entered into this system. This acts as a masterdatabase for the cluster.

There are various stores which use this items table for shopping. Thisentity can be called the POS (point-of-sale) entity. There is anotherentity which is a reporting entity. The different entities areinterested in different aspects of the schema. This leads us to havedifferential index for the tables.

For example, an index on the “Item Name” is more required on a POS wherethe customers enquire about the availability and price of a given itemby name. While a reporting entity may query for all goods which havegone below a specific quantity level, so requires a index on thequantity.

In this scenario role-based index serves as a good performance andmemory improvement measure.

FIG. 18 is a schematic diagram showing an example of applicationscenario 3. In the simple scenario, network load reduced by about 65% (1redo in place of 3 redo); space occupancy reduced by about 10% on master& slave; Master's availability increased by 65% for insert, delete.

It can be seen from the above embodiment that: network traffic will bereduced and hence the bandwidth is effectively utilized; latency isreduced in the master and slaves because of reduced replay/redogeneration; memory is effectively utilized as segment/space for index isallocated, only if the role of the index is matched with the role of thenode; schema is maintained the same in master and slaves, so switch-overis smooth; the differential indexes are online-created, which reducesservice disruption on switch-over.

Embodiment 2

This embodiment of the present invention provides a method for methodfor optimizing index, applied in a subscriber database node. Thisembodiment corresponds to the above embodiment 1, and the same contentwill not be described.

FIG. 19 is a flowchart of the method for optimizing index in accordancewith an embodiment of the present invention; as shown in FIG. 19, themethod includes:

-   -   Step 1901, a subscriber database node updates role information        of the subscriber database node, when the subscriber database        node is added into a cluster containing a master database node;    -   Step 1902, the subscriber database node sends the role        information of the subscriber database node to the master        database node.

In another embodiment, indexes associated with role information areshipped to the subscriber database node from the master database node.

FIG. 20 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention; as shown in FIG.20, the method includes:

-   -   Step 2001, the subscriber database node receives an index from        the master database node;    -   Step 2002, the subscriber database node synchronizes the index        with the master database node.

In another embodiment, a record need to be inserted into a tableassociated with the index. Redo information may be shipped to thesubscriber database node.

FIG. 21 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention; as shown in FIG.21, the method includes:

-   -   Step 2101, the subscriber database node receives redo        information from the master database node;    -   Step 2102, the subscriber database node updates relevant segment        based on the redo information, when the redo information is        index redo information.

As shown in FIG. 21, the method may further include:

-   -   Step 2103, the subscriber database node updates the index with        the redo information, when the redo information is not index        redo information and not dummy redo information.

As shown in FIG. 21, the method may further include:

-   -   Step 2104, the subscriber database node generates index data and        updates the index according to the redo information, when the        redo information is not index redo information and is dummy redo        information.

FIG. 22 is a schematic diagram showing an example of REDO replay in thesubscriber database node. As shown in FIG. 22, when the subscriberdatabase node receives a REDO log from the master database node, thesubscriber database node analyzes if the REDO is a dummy REDO log. Ifthe REDO log is a dummy redo, then the subscriber database noderetrieves the corresponding record from the table (data segment) andinserts it into the index. If the REDO log is not a dummy redo, thesubscriber database node acts as per the REDO log information.

In another embodiment, role information of the subscriber database nodemay be updated.

FIG. 23 is another flowchart of the method for optimizing index inaccordance with an embodiment of the present invention; as shown in FIG.23, the method includes:

-   -   Step 2301, the subscriber database node checks if the role        information of the index matches updated role information of the        subscriber database node, when the role information of the        subscriber database node is updated;    -   Step 2302, the subscriber database node populates the index,        when the role information of the index matches the updated role        information of the subscriber database node.

As shown in FIG. 23, the method may further include:

-   -   Step 2303, the subscriber database node truncates the index,        when the role information of the index does not match the        updated role information of the subscriber database node.

FIG. 24 is a schematic diagram showing an example of role changehandling in the subscriber database node. As shown in FIG. 24, when arole of the subscriber database node is changed, then the data in thenode should reflect the current role. This will involve populating newindexes and truncating (emptying) some irrelevant indexes.

As shown in FIG. 24, when a role is changed, the subscriber databasenode transfers this information to the master database node. Then ititerates through all available indexes and truncates all indexes whichdo not correspond to the current role. It also populates all emptyindexes which correspond to the current role. This index updating isperformed online which does not cause any service disruption.

It can be seen from the above embodiment that: indexes are associatedwith role information, while role information is associated with themaster database node and the subscriber database node.

Furthermore, unnecessary indexes in the master database node and thesubscriber database node are reduced while keeping the schema the same.Furthermore, unnecessary logs transmitted from the master database nodeto the subscriber database node are reduced.

Embodiment 3

This embodiment of the present invention further provides a masterdatabase node. This embodiment corresponds to the method of the aboveembodiment 1, and the same content will not be described.

FIG. 25 is a schematic diagram of the master database node in accordancewith an embodiment of the present invention. As shown in FIG. 25, themaster database node 2500 includes: a first checking unit 2501 and afirst updating unit 2502. Other parts of the master database node canrefer to the existing technology and not be described in the presentapplication. However, it is not limited thereto, and particularimplement way may be determined as actually required.

Where, the first checking unit 2501 is configured to check if roleinformation of an index matches role information of the master databasenode, when the index is added into the master database node; the firstupdating unit 2502 is configured to update database information by usingthe index, when the role information of the index matches the roleinformation of the master database node.

FIG. 26 is another schematic diagram of the master database node inaccordance with an embodiment of the present invention. As shown in FIG.26, the master database node 2600 includes: a first checking unit 2501and a first updating unit 2502, as described in above.

In another embodiment, as shown in FIG. 26, the master database node2600 may further include: a first adding unit 2603, the first addingunit 2603 is configured to add the role information for the masterdatabase node.

As shown in FIG. 26, the master database node 2600 may further include:a first creating unit 2604 and a second updating unit 2605. Where, thefirst creating unit 2604 is configured to create the index by using DataDefinition Language; the second updating unit 2605 is configured toupdate the index with the role information.

In another embodiment, as shown in FIG. 26, the master database node2600 may further include: a second checking unit 2606 and a firstsending unit 2607;

Where, the second checking unit 2606 is configured to check if the roleinformation of the index matches the role information of a subscriberdatabase node; the first sending unit 2607 is configured to send theindex to the subscriber database node when the role information of theindex matches the role information of the subscriber database node, soas to synchronize the index with the subscriber database node.

As shown in FIG. 26, the master database node 2600 may further include:a first receiving unit 2608, the first receiving unit 2608 is configuredto receive the role information of the subscriber database node.

In another embodiment, as shown in FIG. 26, the master database node2600 may further include: a third checking unit 2609 and a firstgenerating unit 2610;

Where, the third checking unit 2609 is configured to check if the roleinformation of the index matches the role information of the masterdatabase node, when a record need to be inserted into a table associatedwith the index; the first generating unit 2610 is configured to generatedummy redo information for the record when the role information of theindex does not match the role information of the master database node.

As shown in FIG. 26, the master database node 2600 may further include:a fourth checking unit 2611 and a second sending unit 2612;

Where, the fourth checking unit 2611 is configured to check if the roleinformation of the index matches the role information of a subscriberdatabase node; the second sending unit 2612 is configured to send thedummy redo information to the subscriber database node when the roleinformation of the index matches the role information of the subscriberdatabase node.

As shown in FIG. 26, the master database node 2600 may further include:a first inserting unit 2613 and a second generating unit 2614:

Where, the first inserting unit 2613 is configured to insert the recordinto the table associated with the index; the second generating unit2614 is configured to generate redo information for the record.

As shown in FIG. 26, the master database node 2600 may further include:a fifth checking unit 2615 and a third sending unit 2616;

Where, the fifth checking unit 2615 is configured to check if the roleinformation of the index matches the role information of a subscriberdatabase node; the third sending unit 2616 is configured to send theredo information to the subscriber database node when the roleinformation of the index matches the role information of the subscriberdatabase node.

In another embodiment, as shown in FIG. 26, the master database node2600 may further include: a sixth checking unit 2617 and a firstpopulating unit 2618;

Where, the sixth checking unit 2617 is configured to check if the roleinformation of the index matches updated role information of the masterdatabase node, when the role information of the master database node isupdated; the first populating unit 2618 is configured to populate theindex when the role information of the index matches the updated roleinformation of the master database node.

It can be seen from the above embodiment that: indexes are associatedwith role information, while role information is associated with themaster database node and the subscriber database node.

Furthermore, unnecessary indexes in the master database node and thesubscriber database node are reduced while keeping the schema the same.Furthermore, unnecessary logs transmitted from the master database nodeto the subscriber database node are reduced.

Embodiment 4

This embodiment of the present invention further provides a subscriberdatabase node. This embodiment corresponds to the method of the aboveembodiment 2, and the same content will not be described.

FIG. 27 is a schematic diagram of the subscriber database node inaccordance with an embodiment of the present invention. As shown in FIG.27, the subscriber database node 2700 includes: a third updating unit2701 and a fourth sending unit 2702. Other parts of the subscriberdatabase node can refer to the existing technology and not be describedin the present application. However, it is not limited thereto, andparticular implement way may be determined as actually required.

Where, the third updating unit 2701 is configured to update roleinformation of the subscriber database node when the subscriber databasenode is added into a cluster containing a master database node; thefourth sending unit 2702 is configured to send the role information ofthe subscriber database node to the master database node.

FIG. 28 is another schematic diagram of the subscriber database node inaccordance with an embodiment of the present invention. As shown in FIG.28, the subscriber database node 2800 includes: a third updating unit2701 and a fourth sending unit 2702, as described in above.

In another embodiment, as shown in FIG. 28, the subscriber database node2800 may further include: a second receiving unit 2803 and asynchronizing unit 2804;

Where, the second receiving unit 2803 is configured to receive an indexfrom the master database node; the synchronizing unit 2804 is configuredto synchronize the index with the master database node.

In another embodiment, as shown in FIG. 28, the subscriber database node2800 may further include: a third receiving unit 2805 and a fourthupdating unit 2806;

Where, the third receiving unit 2805 is configured to receive redoinformation from the master database node; the fourth updating unit 2806is configured to update relevant segment based on the redo information,when the redo information is index redo information.

As shown in FIG. 28, the subscriber database node 2800 may furtherinclude: a fifth updating unit 2807, the fifth updating unit 2807 isconfigured to update the index with the redo information, when the redoinformation is not index redo information and not dummy redoinformation.

As shown in FIG. 28, the subscriber database node 2800 may furtherinclude: a second generating unit 2808, the second generating unit 2808is configured to generate index data and update the index according tothe redo information, when the redo information is not index redoinformation and is dummy redo information.

In another embodiment, as shown in FIG. 28, the subscriber database node2800 may further include: a seventh checking unit 2809 and a secondpopulating unit 2810;

Where, the seventh checking unit 2809 is configured to check if the roleinformation of the index matches updated role information of thesubscriber database node, when the role information of the subscriberdatabase node is updated; the second populating unit 2810 is configuredto populate the index when the role information of the index matches theupdated role information of the subscriber database node.

As shown in FIG. 28, the subscriber database node 2800 may furtherinclude: a truncating unit 2811, the truncating unit 2811 is configuredto truncate the index, when the role information of the index does notmatch the updated role information of the subscriber database node.

It can be seen from the above embodiment that: indexes are associatedwith role information, while role information is associated with themaster database node and the subscriber database node.

Furthermore, unnecessary indexes in the master database node and thesubscriber database node are reduced while keeping the schema the same.Furthermore, unnecessary logs transmitted from the master database nodeto the subscriber database node are reduced.

It should be understood that each of the parts of the present inventionmay be implemented by hardware, software, firmware, or a combinationthereof. In the above embodiments, multiple steps or methods may berealized by software or firmware that is stored in the memory andexecuted by an appropriate instruction executing system. For example, ifit is realized by hardware, it may be realized by any one of thefollowing technologies known in the art or a combination thereof as inanother embodiment: a discrete logic circuit having a logic gate circuitfor realizing logic functions of data signals, application-specificintegrated circuit having an appropriate combined logic gate circuit, aProgrammable Gate Array (PGA), and a field programmable gate array(FPGA), etc.

The description or blocks in the flowcharts or of any process or methodin other manners may be understood as being indicative of comprising oneor more modules, segments or parts for realizing the codes of executableinstructions of the steps in specific logic functions or processes, andthat the scope of the embodiments of the present invention compriseother implementations, wherein the functions may be executed in mannersdifferent from those shown or discussed, including executing thefunctions according to the related functions in a substantiallysimultaneous manner or in a reverse order, which should be understood bythose skilled in the art to which the present invention pertains.

The logic and/or steps shown in the flowcharts or described in othermanners here may be, for example, understood as a sequencing list ofexecutable instructions for realizing logic functions, which may beimplemented in any computer readable medium, for use by an instructionexecuting system, device or apparatus (such as a system including acomputer, a system including a processor, or other systems capable ofextracting instructions from an instruction executing system, device orapparatus and executing the instructions), or for use in combinationwith the instruction executing system, device or apparatus.

The above literal description and drawings show various features of thepresent invention. It should be understood that those skilled in the artmay prepare appropriate computer codes to carry out each of the stepsand processes as described above and shown in the drawings. It should bealso understood that all the terminals, computers, servers, and networksmay be any type, and the computer codes may be prepared according to thedisclosure to carry out the present invention by using the apparatus.

Particular embodiments of the present invention have been disclosedherein. Those skilled in the art will readily recognize that the presentinvention is applicable in other environments. In practice, there existmany embodiments and implementations. The appended claims are by nomeans intended to limit the scope of the present invention to the aboveparticular embodiments. Furthermore, any reference to “a device to . . .” is an explanation of device plus function for describing elements andclaims, and it is not desired that any element using no reference to “adevice to . . . ” is understood as an element of device plus function,even though the wording of “device” is included in that claim.

Although a particular embodiment has been shown and the presentinvention has been described, it is obvious that equivalentmodifications and variants are conceivable to those skilled in the artin reading and understanding the description and drawings. Especiallyfor various functions executed by the above elements (portions,assemblies, apparatus, and compositions, etc.), except otherwisespecified, it is desirable that the terms (including the reference to“device”) describing these elements correspond to any element executingparticular functions of these elements (i.e. functional equivalents),even though the element is different from that executing the function ofan exemplary embodiment or embodiments illustrated in the presentinvention with respect to structure. Furthermore, although the aparticular feature of the present invention is described with respect toonly one or more of the illustrated embodiments, such a feature may becombined with one or more other features of other embodiments as desiredand in consideration of advantageous aspects of any given or particularapplication.

What is claimed is:
 1. A method for optimizing index, comprising:checking, by a master database node, if role information of an indexmatches role information of the master database node, when the index isadded into the master database node; updating, by the master databasenode, database information by using the index when the role informationof the index matches the role information of the master database node.2. The method as claimed in claim 1, wherein before checking if roleinformation of a index matches role information of the master databasenode, the method further comprises: adding the role information for themaster database node.
 3. The method as claimed in claim 1, whereinbefore checking if role information of an index matches role informationof the master database node, the method further comprises: creating theindex by using Data Definition Language; updating the index with therole information.
 4. The method as claimed in claim 1, wherein themethod further comprises: checking, by the master database node, if therole information of the index matches the role information of asubscriber database node; sending, by the master database node, theindex to the subscriber database node when the role information of theindex matches the role information of the subscriber database node, soas to synchronize the index with the subscriber database node.
 5. Themethod as claimed in claim 4, wherein the method further comprises:receiving, by the master database node, the role information of thesubscriber database node.
 6. The method as claimed in claim 1, whereinthe method further comprises: checking, by the master database node, ifthe role information of the index matches the role information of themaster database node, when a record need to be inserted into a tableassociated with the index; generating dummy redo information for therecord when the role information of the index does not match the roleinformation of the master database node.
 7. The method as claimed inclaim 6, wherein the method further comprises: checking, by the masterdatabase node, if the role information of the index matches the roleinformation of a subscriber database node; sending, by the masterdatabase node, the dummy redo information to the subscriber databasenode when the role information of the index matches the role informationof the subscriber database node.
 8. The method as claimed in claim 6,wherein when the role information of the index matches the roleinformation of the master database node, the method further comprises:inserting, by the master database node, the record into the tableassociated with the index; generating redo information for the record.9. The method as claimed in claim 8, wherein the method furthercomprises: checking, by the master database node, if the roleinformation of the index matches the role information of a subscriberdatabase node: sending, by the master database node, the redoinformation to the subscriber database node when the role information ofthe index matches the role information of the subscriber database node.10. The method as claimed in claim 1, wherein the method furthercomprises: checking, by the master database node, if the roleinformation of the index matches updated role information of the masterdatabase node, when the role information of the master database node isupdated; populating, by the master database node, the index when therole information of the index matches the updated role information ofthe master database node.
 11. A method for optimizing index, comprising:updating, by a subscriber database node, role information of thesubscriber database node when the subscriber database node is added intoa cluster containing a master database node; sending, by the subscriberdatabase node, the role information of the subscriber database node tothe master database node.
 12. The method as claimed in claim 11, whereinthe method further comprises: receiving, by the subscriber databasenode, an index from the master database node; synchronizing the indexwith the master database node.
 13. The method as claimed in claim 11,wherein the method further comprises: receiving, by the subscriberdatabase node, redo information from the master database node; updatingrelevant segment based on the redo information, when the redoinformation is index redo information.
 14. The method as claimed inclaim 13, wherein the method further comprises: updating the index withthe redo information, when the redo information is not index redoinformation and not dummy redo information.
 15. The method as claimed inclaim 13, wherein the method further comprises: generating index dataand updating the index according to the redo information, when the redoinformation is not index redo information and is dummy redo information.16. The method as claimed in claim 11, wherein the method furthercomprises: checking, by the subscriber database node, if the roleinformation of the index matches updated role information of thesubscriber database node, when the role information of the subscriberdatabase node is updated; populating, by the subscriber database node,the index when the role information of the index matches the updatedrole information of the subscriber database node.
 17. The method asclaimed in claim 16, wherein the method further comprises: truncating,by the subscriber database node, the index when the role information ofthe index does not match the updated role information of the subscriberdatabase node.
 18. A master database node, comprising: a processor; amemory storing computer program which are reading by the processor tocause the processor to perform the following steps; checking if roleinformation of an index matches role information of the master databasenode, when the index is added into the master database node; updatingdatabase information by using the index when the role information of theindex matches the role information of the master database node.
 19. Themaster database node according to claim 18, wherein the processorfurther performs the following steps: checking if the role informationof the index matches the role information of a subscriber database node;sending the index to the subscriber database node when the roleinformation of the index matches the role information of the subscriberdatabase node, so as to synchronize the index with the subscriberdatabase node.
 20. A subscriber database node, comprising: a processor;a memory storing computer program which are reading by the processor tocause the processor to perform the following steps: updating roleinformation of the subscriber database node when the subscriber databasenode is added into a cluster containing a master database node; sendingthe role information of the subscriber database node to the masterdatabase node.